# coding = utf-8
"""
@autor: Felix
"""
import json
import pymysql
import requests

header = {
    'User-Agent': 'Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Mobile Safari/537.36'
}

mydb = pymysql.connect(host='localhost', user='root', passwd='123456', db='ods', port=3308, charset='utf8')
mycursor = mydb.cursor()

'''
建表语句
DROP TABLE IF EXISTS `districtsx`;
CREATE TABLE `districtsx`  (
  `districtId` int(11) NOT NULL AUTO_INCREMENT,
  `districtPid` int(11) NULL DEFAULT NULL COMMENT '上级ID',
  `pname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '上级名称_省',
  `cityname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '上级名称_市',
  `districtname` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '上级名称_区县',
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '行政区名称',
  `citycode` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '城市编码',
  `adcode` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '城市区域编码',
  `lng` float(13, 10) NULL DEFAULT NULL COMMENT '经度',
  `lat` float(13, 10) NULL DEFAULT NULL COMMENT '纬度',
  `level` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '行政区划级别',
  `createTime` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
  `updateTime` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`districtId`) USING BTREE,
  INDEX `districtsx_idx1`(`name`) USING BTREE,
  INDEX `districtsx_idx2`(`districtId`) USING BTREE,
  INDEX `districtsx_idx3`(`cityname`) USING BTREE,
  INDEX `districtsx_idx4`(`districtname`) USING BTREE,
  INDEX `districtsx_idx5`(`districtPid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 44216 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
'''

mycursor.execute("truncate table districtsx")
mycursor.execute("alter table districtsx auto_increment = 1")

insert_sql = "INSERT INTO `districtsx` (`districtpid`,`pname`,`cityname`, `districtname`, `name`, `adcode`, `lng`, `lat`, `level`) VALUES  (%s, %s, %s, %s, %s, %s, %s, %s, %s);"
insert_city_sql = "INSERT INTO `districtsx` ( `districtpid`,`pname`,`cityname`, `districtname`, `name`, `citycode`,`adcode`, `lng`, `lat`, `level`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"

select_sql = 'select districtId from districtsx where adcode = %s'


def updatedb(sql, *args):
    try:
        mycursor.execute(sql, args)
        mydb.commit()
    except Exception as e:
        print(e)

def selectdb(sql, *args):
    mycursor.execute(sql, args)
    return mycursor


updatedb(insert_sql, 0, '中华人民共和国', '中国', '中国', '中国', '100000', '116.3683244', '39.915085', 'country')
provinces = ['北京市', '天津市', '河北省', '山西省', '内蒙古自治区', '辽宁省', '吉林省', '黑龙江省', '上海市', '江苏省', '浙江省', '安徽省', '福建省', '江西省',
             '山东省', '河南省', '湖北省', '湖南省', '广东省', '广西壮族自治区', '海南省', '重庆市', '四川省', '贵州省', '云南省', '西藏自治区', '陕西省', '甘肃省',
             '青海省', '宁夏回族自治区', '新疆维吾尔自治区', '台湾省', '香港特别行政区', '澳门特别行政区']
for i in provinces:
    code_url = 'https://restapi.amap.com/v3/config/district?key=aa9bb811e91d316791928a6480bba5ad&keywords={}&subdistrict=3&extensions=base'.format(i)
    res = requests.get(code_url, headers=header)
    print(code_url)
    print(res.text)
    province = json.loads(res.text)['districts']
    adcode = province[0]['adcode']
    pname = province[0]['name']
    center = province[0]['center']
    pcitycode = province[0]['citycode']
    level = province[0]['level']
    lng = province[0]['center'].split(',')[0]
    lat = province[0]['center'].split(',')[1]
    city_list = province[0]['districts']

    print(pname, pcitycode, adcode, lng, lat, level)
    updatedb(insert_sql, 1, pname, pname, pname, pname, adcode, lng, lat, level)
    districtpid = selectdb(select_sql, adcode).fetchone()[0]
    # print(city_list)
    for city in city_list:
        citycode = city['citycode']
        adcode = city['adcode']
        name_c = city['name']
        level = city['level']
        lng = city['center'].split(',')[0]
        lat = city['center'].split(',')[1]
        district_list = city['districts']
        # print(name, citycode, adcode, lng, lat, level)
        updatedb(insert_city_sql, districtpid, pname, name_c, name_c, name_c, citycode, adcode, lng, lat, level)
        citypid = selectdb(select_sql, adcode).fetchone()[0]
        for district in district_list:
            citycode = district['citycode']
            adcode = district['adcode']
            name_d = district['name']
            level = district['level']
            lng = district['center'].split(',')[0]
            lat = district['center'].split(',')[1]
            street_list = district['districts']
            if level in ['district']:
                updatedb(insert_city_sql, citypid, pname, name_c, name_d, name_d, citycode, adcode, lng, lat, level)
                print(pname, name_c, name_d, name_d, citycode, adcode, lng, lat, level)
                streetpid = selectdb(select_sql, adcode).fetchone()[0]
                for street in street_list:
                    citycode = street['citycode']
                    adcode = street['adcode']
                    name_s = street['name']
                    level = street['level']
                    lng = street['center'].split(',')[0]
                    lat = street['center'].split(',')[1]
                    # street_list = street['districts']
                    if level in ['street']:
                        updatedb(insert_city_sql, streetpid, pname, name_c, name_d, name_s, citycode, adcode, lng, lat, level)
                        print(pname, name_c, name_d, name_s, citycode, adcode, lng, lat, level)
            elif level in ['street']:
                updatedb(insert_city_sql, citypid, pname, name_c, '', name_d, citycode, adcode, lng, lat, level)
                print(pname, name_c, name_d, name_d, citycode, adcode, lng, lat, level)
print('采集完成')
mycursor.execute("SELECT COUNT(1) FROM districtsx")
data = mycursor.fetchone()
print("生成记录数 : %s " % data)